{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial 003: Pandas Fundementals\n",
    "## Overview\n",
    "In this short tutorial, we will explain the utilities of Pandas. The contents of this tutorial includes\n",
    "1. Read and write CSV dataset\n",
    "2. Basic data manipulation\n",
    "3. Apply and Groupby function\n",
    "4. Joins\n",
    "\n",
    "## Data\n",
    "The data can be donwloaded here (https://www.stats.govt.nz/assets/Uploads/New-Zealand-business-demography-statistics/New-Zealand-business-demography-statistics-At-February-2019/Download-data/geographic-units-by-industry-and-statistical-area-2000-19-descending-order.zip)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read and Write"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "in_file = \"Data7602DescendingYearOrder.csv\"\n",
    "data = pd.read_csv(in_file, engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>96</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>198</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155181</th>\n",
       "      <td>Total</td>\n",
       "      <td>T074</td>\n",
       "      <td>2019</td>\n",
       "      <td>2049</td>\n",
       "      <td>6400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155182</th>\n",
       "      <td>Total</td>\n",
       "      <td>T075</td>\n",
       "      <td>2019</td>\n",
       "      <td>5151</td>\n",
       "      <td>27600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155183</th>\n",
       "      <td>Total</td>\n",
       "      <td>T076</td>\n",
       "      <td>2019</td>\n",
       "      <td>201255</td>\n",
       "      <td>799100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155184</th>\n",
       "      <td>Total</td>\n",
       "      <td>T999</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155185</th>\n",
       "      <td>Total</td>\n",
       "      <td>TTotal</td>\n",
       "      <td>2019</td>\n",
       "      <td>582483</td>\n",
       "      <td>2284000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5155186 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        anzsic06     Area  year  geo_count  ec_count\n",
       "0              A  A100100  2000         96       130\n",
       "1              A  A100200  2000        198       110\n",
       "2              A  A100300  2000         42        25\n",
       "3              A  A100400  2000         66        40\n",
       "4              A  A100500  2000         63        40\n",
       "...          ...      ...   ...        ...       ...\n",
       "5155181    Total     T074  2019       2049      6400\n",
       "5155182    Total     T075  2019       5151     27600\n",
       "5155183    Total     T076  2019     201255    799100\n",
       "5155184    Total     T999  2019          3         0\n",
       "5155185    Total   TTotal  2019     582483   2284000\n",
       "\n",
       "[5155186 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5155186, 5)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['anzsic06', 'Area', 'year', 'geo_count', 'ec_count'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"out_file.csv\", index=False, encoding='utf-8', sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "year = data['year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          2000\n",
       "1          2000\n",
       "2          2000\n",
       "3          2000\n",
       "4          2000\n",
       "           ... \n",
       "5155181    2019\n",
       "5155182    2019\n",
       "5155183    2019\n",
       "5155184    2019\n",
       "5155185    2019\n",
       "Name: year, Length: 5155186, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_count = data['geo_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           True\n",
       "1           True\n",
       "2          False\n",
       "3          False\n",
       "4          False\n",
       "           ...  \n",
       "5155181     True\n",
       "5155182     True\n",
       "5155183     True\n",
       "5155184    False\n",
       "5155185     True\n",
       "Name: geo_count, Length: 5155186, dtype: bool"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo_count > 90"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "geo_count[geo_count > 90]=90"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  anzsic06     Area  year  geo_count  ec_count\n",
       "0        A  A100100  2000         90       130\n",
       "1        A  A100200  2000         90       110\n",
       "2        A  A100300  2000         42        25\n",
       "3        A  A100400  2000         66        40\n",
       "4        A  A100500  2000         63        40"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def myfunc(x):\n",
    "    if x > 90:\n",
    "        return 90\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['ec_count'] = data['ec_count'].apply(myfunc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  anzsic06     Area  year  geo_count  ec_count\n",
       "0        A  A100100  2000         90        90\n",
       "1        A  A100200  2000         90        90\n",
       "2        A  A100300  2000         42        25\n",
       "3        A  A100400  2000         66        40\n",
       "4        A  A100500  2000         63        40"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['ec_count'] = data['ec_count'].apply(lambda x: 85 if x > 85 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  anzsic06     Area  year  geo_count  ec_count\n",
       "0        A  A100100  2000         90        85\n",
       "1        A  A100200  2000         90        85\n",
       "2        A  A100300  2000         42        25\n",
       "3        A  A100400  2000         66        40\n",
       "4        A  A100500  2000         63        40"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['A', 'A01', 'A011', 'A012', 'A013', 'A014', 'A015', 'A016', 'A017',\n",
       "       'A018', 'A019', 'A02', 'A020', 'A03', 'A030', 'A04', 'A041',\n",
       "       'A042', 'A05', 'A051', 'A052', 'B', 'B06', 'B060', 'B07', 'B070',\n",
       "       'B08', 'B080', 'B09', 'B091', 'B099', 'B10', 'B101', 'B109', 'C',\n",
       "       'C11', 'C111', 'C112', 'C113', 'C114', 'C115', 'C116', 'C117',\n",
       "       'C118', 'C119', 'C12', 'C121', 'C122', 'C13', 'C131', 'C132',\n",
       "       'C133', 'C134', 'C135', 'C14', 'C141', 'C149', 'C15', 'C151',\n",
       "       'C152', 'C16', 'C161', 'C162', 'C17', 'C170', 'C18', 'C181',\n",
       "       'C182', 'C183', 'C184', 'C185', 'C189', 'C19', 'C191', 'C192',\n",
       "       'C20', 'C201', 'C202', 'C203', 'C209', 'C21', 'C211', 'C212',\n",
       "       'C213', 'C214', 'C22', 'C221', 'C222', 'C223', 'C224', 'C229',\n",
       "       'C23', 'C231', 'C239', 'C24', 'C241', 'C242', 'C243', 'C244',\n",
       "       'C245', 'C246', 'C249', 'C25', 'C251', 'C259', 'D', 'D26', 'D261',\n",
       "       'D262', 'D263', 'D264', 'D27', 'D270', 'D28', 'D281', 'D29',\n",
       "       'D291', 'D292', 'E', 'E30', 'E301', 'E302', 'E31', 'E310', 'E32',\n",
       "       'E321', 'E322', 'E323', 'E324', 'E329', 'F', 'F33', 'F331', 'F332',\n",
       "       'F333', 'F34', 'F341', 'F349', 'F35', 'F350', 'F36', 'F360', 'F37',\n",
       "       'F371', 'F372', 'F373', 'F38', 'F380', 'G', 'G39', 'G391', 'G392',\n",
       "       'G40', 'G400', 'G41', 'G411', 'G412', 'G42', 'G421', 'G422',\n",
       "       'G423', 'G424', 'G425', 'G426', 'G427', 'G43', 'G431', 'G432', 'H',\n",
       "       'H44', 'H440', 'H45', 'H451', 'H452', 'H453', 'I', 'I46', 'I461',\n",
       "       'I462', 'I47', 'I471', 'I472', 'I48', 'I481', 'I482', 'I49',\n",
       "       'I490', 'I50', 'I501', 'I502', 'I51', 'I510', 'I52', 'I521',\n",
       "       'I522', 'I529', 'I53', 'I530', 'J', 'J54', 'J541', 'J542', 'J55',\n",
       "       'J551', 'J552', 'J56', 'J561', 'J562', 'J57', 'J570', 'J58',\n",
       "       'J580', 'J59', 'J591', 'J592', 'J60', 'J601', 'J602', 'K', 'K62',\n",
       "       'K621', 'K622', 'K623', 'K624', 'K63', 'K631', 'K632', 'K633',\n",
       "       'K64', 'K641', 'K642', 'L', 'L66', 'L661', 'L662', 'L663', 'L664',\n",
       "       'L67', 'L671', 'L672', 'M', 'M69', 'M691', 'M692', 'M693', 'M694',\n",
       "       'M695', 'M696', 'M697', 'M699', 'M70', 'M700', 'N', 'N72', 'N721',\n",
       "       'N722', 'N729', 'N73', 'N731', 'N732', 'O', 'O75', 'O751', 'O753',\n",
       "       'O754', 'O76', 'O760', 'O77', 'O771', 'O772', 'P', 'P80', 'P801',\n",
       "       'P802', 'P81', 'P810', 'P82', 'P821', 'P822', 'Q', 'Q84', 'Q840',\n",
       "       'Q85', 'Q851', 'Q852', 'Q853', 'Q859', 'Q86', 'Q860', 'Q87',\n",
       "       'Q871', 'Q879', 'R', 'R89', 'R891', 'R892', 'R90', 'R900', 'R91',\n",
       "       'R911', 'R912', 'R913', 'R92', 'R920', 'S', 'S94', 'S941', 'S942',\n",
       "       'S949', 'S95', 'S951', 'S952', 'S953', 'S954', 'S955', 'Total'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['anzsic06'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby = data.groupby('anzsic06').agg({'geo_count':['mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>geo_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>anzsic06</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A01</th>\n",
       "      <td>28.261749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A011</th>\n",
       "      <td>6.082024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A012</th>\n",
       "      <td>6.704050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A013</th>\n",
       "      <td>11.424234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S952</th>\n",
       "      <td>3.756373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S953</th>\n",
       "      <td>4.735772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S954</th>\n",
       "      <td>4.394816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S955</th>\n",
       "      <td>6.170018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>316 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          geo_count\n",
       "               mean\n",
       "anzsic06           \n",
       "A         28.580359\n",
       "A01       28.261749\n",
       "A011       6.082024\n",
       "A012       6.704050\n",
       "A013      11.424234\n",
       "...             ...\n",
       "S952       3.756373\n",
       "S953       4.735772\n",
       "S954       4.394816\n",
       "S955       6.170018\n",
       "Total     82.192986\n",
       "\n",
       "[316 rows x 1 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby.columns = groupby.columns.droplevel(0)\n",
    "groupby.rename(columns={ groupby.columns[0]: \"anzsic06_mean\" }, inplace = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06_mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>anzsic06</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A01</th>\n",
       "      <td>28.261749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A011</th>\n",
       "      <td>6.082024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A012</th>\n",
       "      <td>6.704050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A013</th>\n",
       "      <td>11.424234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S952</th>\n",
       "      <td>3.756373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S953</th>\n",
       "      <td>4.735772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S954</th>\n",
       "      <td>4.394816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S955</th>\n",
       "      <td>6.170018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>316 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          anzsic06_mean\n",
       "anzsic06               \n",
       "A             28.580359\n",
       "A01           28.261749\n",
       "A011           6.082024\n",
       "A012           6.704050\n",
       "A013          11.424234\n",
       "...                 ...\n",
       "S952           3.756373\n",
       "S953           4.735772\n",
       "S954           4.394816\n",
       "S955           6.170018\n",
       "Total         82.192986\n",
       "\n",
       "[316 rows x 1 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.merge(data, groupby, how='left', on='anzsic06')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "      <th>anzsic06_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  anzsic06     Area  year  geo_count  ec_count  anzsic06_mean\n",
       "0        A  A100100  2000         90        85      28.580359\n",
       "1        A  A100200  2000         90        85      28.580359\n",
       "2        A  A100300  2000         42        25      28.580359\n",
       "3        A  A100400  2000         66        40      28.580359\n",
       "4        A  A100500  2000         63        40      28.580359"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "      <th>anzsic06_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155181</th>\n",
       "      <td>Total</td>\n",
       "      <td>T074</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155182</th>\n",
       "      <td>Total</td>\n",
       "      <td>T075</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155183</th>\n",
       "      <td>Total</td>\n",
       "      <td>T076</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155184</th>\n",
       "      <td>Total</td>\n",
       "      <td>T999</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155185</th>\n",
       "      <td>Total</td>\n",
       "      <td>TTotal</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5155186 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        anzsic06     Area  year  geo_count  ec_count  anzsic06_mean\n",
       "0              A  A100100  2000         90        85      28.580359\n",
       "1              A  A100200  2000         90        85      28.580359\n",
       "2              A  A100300  2000         42        25      28.580359\n",
       "3              A  A100400  2000         66        40      28.580359\n",
       "4              A  A100500  2000         63        40      28.580359\n",
       "...          ...      ...   ...        ...       ...            ...\n",
       "5155181    Total     T074  2019         90        85      82.192986\n",
       "5155182    Total     T075  2019         90        85      82.192986\n",
       "5155183    Total     T076  2019         90        85      82.192986\n",
       "5155184    Total     T999  2019          3         0      82.192986\n",
       "5155185    Total   TTotal  2019         90        85      82.192986\n",
       "\n",
       "[5155186 rows x 6 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_part_1 = data.iloc[0:1000]\n",
    "data_part_2 = data.iloc[1000:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_stacked = data_part_1.append(data_part_2, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5155186, 6)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_stacked.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new_col = data['ec_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_concat = pd.concat([data, data_new_col], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anzsic06</th>\n",
       "      <th>Area</th>\n",
       "      <th>year</th>\n",
       "      <th>geo_count</th>\n",
       "      <th>ec_count</th>\n",
       "      <th>anzsic06_mean</th>\n",
       "      <th>ec_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A100100</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A100200</td>\n",
       "      <td>2000</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>28.580359</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A100300</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "      <td>28.580359</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>A100400</td>\n",
       "      <td>2000</td>\n",
       "      <td>66</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>A100500</td>\n",
       "      <td>2000</td>\n",
       "      <td>63</td>\n",
       "      <td>40</td>\n",
       "      <td>28.580359</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155181</th>\n",
       "      <td>Total</td>\n",
       "      <td>T074</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155182</th>\n",
       "      <td>Total</td>\n",
       "      <td>T075</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155183</th>\n",
       "      <td>Total</td>\n",
       "      <td>T076</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155184</th>\n",
       "      <td>Total</td>\n",
       "      <td>T999</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>82.192986</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5155185</th>\n",
       "      <td>Total</td>\n",
       "      <td>TTotal</td>\n",
       "      <td>2019</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>82.192986</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5155186 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        anzsic06     Area  year  geo_count  ec_count  anzsic06_mean  ec_count\n",
       "0              A  A100100  2000         90        85      28.580359        85\n",
       "1              A  A100200  2000         90        85      28.580359        85\n",
       "2              A  A100300  2000         42        25      28.580359        25\n",
       "3              A  A100400  2000         66        40      28.580359        40\n",
       "4              A  A100500  2000         63        40      28.580359        40\n",
       "...          ...      ...   ...        ...       ...            ...       ...\n",
       "5155181    Total     T074  2019         90        85      82.192986        85\n",
       "5155182    Total     T075  2019         90        85      82.192986        85\n",
       "5155183    Total     T076  2019         90        85      82.192986        85\n",
       "5155184    Total     T999  2019          3         0      82.192986         0\n",
       "5155185    Total   TTotal  2019         90        85      82.192986        85\n",
       "\n",
       "[5155186 rows x 7 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
